package com.engine.kq.cmd.balanceofleaverp;

import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.engine.kq.biz.*;
import com.engine.kq.util.KQTransMethod;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;
import weaver.common.DateUtil;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.TimeUtil;
import weaver.general.Util;
import weaver.hrm.User;
import weaver.hrm.company.DepartmentComInfo;
import weaver.hrm.company.SubCompanyComInfo;
import weaver.hrm.job.JobTitlesComInfo;
import weaver.systeminfo.SystemEnv;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 员工假期余额报表--将查询结果导出为Excel文件
 */
public class ExportExcelCmd extends AbstractCommonCommand<Map<String, Object>> {

    private HttpServletRequest request;
    private HttpServletResponse response;

    public ExportExcelCmd(Map<String, Object> params, User user, HttpServletRequest request, HttpServletResponse response) {
        this.user = user;
        this.params = params;
        this.request = request;
        this.response = response;
    }

    @Override
    public BizLogContext getLogContext() {
        return null;
    }

    @Override
    public Map<String, Object> execute(CommandContext commandContext) {
        long startTime = System.currentTimeMillis();
        Map<String, Object> resultMap = new HashMap<String, Object>();
        /**
         * 展示列
         */
        String displayColumn = Util.null2String(params.get("displayColumn"));
        List<String> displayColumnList = Util.TokenizerString(displayColumn, ",");
        /**
         * 时间范围选择的年份
         * dateScope：5-本年、8-上一年
         * selectedYear：指定年份
         */
        String dateScope = Util.null2String(params.get("dateScope"));
        String selectedYear = Util.null2String(params.get("selectedYear"));
        if (dateScope.equals("5") || dateScope.equals("8")) {
            selectedYear = TimeUtil.getDateByOption(dateScope, "0").substring(0, 4);
        }
        /**
         * 数据范围
         * dataScope：0-总部、1-分部、2-分部、3-人员、4-我的下属
         * subcomId：指定分部ID
         * deptId：指定部门ID
         * resourceId：指定人员ID
         * allLevel：是否包含下级下属：0-不包含、1-包含
         */
        String dataScope = Util.null2String(params.get("dataScope"));
        String subcomId = Util.null2String(params.get("subcomId"));
        String deptId = Util.null2String(params.get("deptId"));
        String resourceId = Util.null2String(params.get("resourceId"));
        String allLevel = Util.null2String(params.get("allLevel"));
        //人员状态
        String resourceStatus = Util.null2String(params.get("status"));
        /**
         * isNoAccount：是否显示无账号人员：true-显示、false-不显示
         */
        String isNoAccount = Util.null2String(params.get("isNoAccount"));

        try {
            /**
             * 获取考勤报表权限共享设置
             */
            KQReportBiz kqReportBiz = new KQReportBiz();
            String rightStr = kqReportBiz.getReportRight("4", "" + user.getUID(), "a");

            /**
             * 拼凑查询结果Excel文件的表头
             */
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(SystemEnv.getHtmlLabelName(389441, user.getLanguage()));//员工假期余额
            // 设置字体
            Font font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short) 14);
            //字体加粗
            font.setBold(true);
            //设置字体名字
            font.setFontName("宋体");
            //设置样式;
            CellStyle style = workbook.createCellStyle();
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(IndexedColors.BLACK.index);
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(IndexedColors.BLACK.index);
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(IndexedColors.BLACK.index);
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(IndexedColors.BLACK.index);
            //在样式用应用设置的字体;
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            int index = 0;
            HSSFRow headerRow = sheet.createRow(0);
            String[] tempArr = new String[]{SystemEnv.getHtmlLabelName(413, user.getLanguage()) + ",lastName", SystemEnv.getHtmlLabelName(141, user.getLanguage()) + ",subcom", SystemEnv.getHtmlLabelName(124, user.getLanguage()) + ",dept", SystemEnv.getHtmlLabelName(6086, user.getLanguage()) + ",jobtitle", SystemEnv.getHtmlLabelName(714, user.getLanguage()) + ",workcode"};
            for (int i = 0; i < tempArr.length; i++) {
                String[] fieldInfo = tempArr[i].split(",");
                if (fieldInfo[1].equals("lastName") || displayColumnList.indexOf(fieldInfo[1]) > -1) {
                    headerRow.setHeight((short) 312);
                    HSSFCell headCell = headerRow.createCell(index);
                    headCell.setCellValue(fieldInfo[0]);
                    headCell.setCellStyle(style);
                    sheet.setColumnWidth((short) index, (short) (6 * 1000));
                    index++;
                }
            }

            /**********************************************************************************************************/

            /**获取假期类型的相关设置*/

            /*请假类型的ID*/
            String leaveRulesId = "";

            /*假期类型的名称，用作查询结果列表的表头显示*/
            String leaveName = "";

            /*最小请假单位：1-按天请假、2-按半天请假、3-按小时请假、4-按整天请假*/
            int minimumUnit = 1;

            /*请假单位的显示名称是天还是小时*/
            String unitName = "";

            /*是否开启假期余额，没有开启假期余额时需要提示“不限制余额*/
            int balanceEnable = 0;

            /**********************************************************************************************************/

            /*是否是混合模式(福利年假+法定年假)*/
            boolean isMixMode = false;

            //是否是调休
            boolean isTiaoXiu = false;

            String wjnjid = new BaseBean().getPropValue("ss_prop_zm", "foreign_annual_leave_rule_id");

            /**********************************************************************************************************/

            /*获取当前日期*/
            String currentDate = DateUtil.getCurrentDate();

            Map<String, BigDecimal> balanceMap = new HashMap<String, BigDecimal>();
            KQLeaveRulesComInfo rulesComInfo = new KQLeaveRulesComInfo();
            rulesComInfo.setTofirstRow();
            while (rulesComInfo.next()) {
                /*该假期没有启用，不显示*/
                if (!rulesComInfo.getIsEnable().equals("1")) {
                    continue;
                }
                /*展示列不展示，不显示*/
                if (displayColumnList.indexOf(rulesComInfo.getId()) < 0) {
                    continue;
                }
                /*获取假期类型的设置 start*/
                leaveRulesId = rulesComInfo.getId();
                leaveName = Util.formatMultiLang(rulesComInfo.getLeaveName(), "" + user.getLanguage());
                minimumUnit = Util.getIntValue(rulesComInfo.getMinimumUnit(), 1);
                if (minimumUnit == 1 || minimumUnit == 2 || minimumUnit == 4) {
                    unitName = SystemEnv.getHtmlLabelName(389325, user.getLanguage());//(天)
                } else {
                    unitName = SystemEnv.getHtmlLabelName(389326, user.getLanguage());//(小时)
                }
                String showName = leaveName + unitName;
                balanceEnable = Util.getIntValue(rulesComInfo.getBalanceEnable(), 0);

                isMixMode = KQLeaveRulesBiz.isMixMode(leaveRulesId);
                isTiaoXiu = KQLeaveRulesBiz.isTiaoXiu(leaveRulesId);
                /*获取假期类型的设置 end*/

                headerRow.setHeight((short) 312);
                HSSFCell headCell = headerRow.createCell(index);
                headCell.setCellValue(showName);
                headCell.setCellStyle(style);
                sheet.setColumnWidth((short) index, (short) (6 * 1000));
                index++;

                /*判断该假期规则是否开启了假期余额，若没有开启，则无需查找该假期的余额，直接遍历下一个假期*/
                if (balanceEnable == 0) {
                    continue;
                }
                //如果是调休，获取假期余额的方式有些不同
                if (isTiaoXiu) {
                    params.put("leaveRulesId", leaveRulesId);
                    params.put("showAll", "true");
                    Map<String, BigDecimal> _balanceMap = KQBalanceOfLeaveBiz.getRestAmountMapByDis5(params, user);
                    balanceMap.putAll(_balanceMap);
                    continue;
                }

                boolean isWjnj = false;
                if (StringUtils.equals(leaveRulesId, wjnjid)) {
                    isWjnj = true;
                }

                /*查询指定年份指定假期的余额数据，并放入集合中，用于后续拼凑查询结果数据*/
                KQTransMethod transMethod = new KQTransMethod();
                RecordSet recordSet = new RecordSet();
                String sql = "select a.id hrmResourceId,b.* from HrmResource a left join kq_balanceOfLeave b on a.id=b.resourceId " +
                        "and belongYear='" + selectedYear + "' and b.leaveRulesId=" + leaveRulesId + " where 1=1 ";
                if (dataScope.equals("0")) {
                    //总部
                } else if (dataScope.equals("1")) {
                    sql += " and a.subcompanyId1 in (" + subcomId + ") ";
                } else if (dataScope.equals("2")) {
                    sql += " and a.departmentId in (" + deptId + ") ";
                } else if (dataScope.equals("3")) {
                    sql += " and a.id in (" + resourceId + ")";
                } else if (dataScope.equals("4")) {
                    if (allLevel.equals("1")) {
                        sql += " and (a.id=" + user.getUID() + " or a.managerStr like '%," + user.getUID() + ",%' )";
                    } else {
                        sql += " and (a.id=" + user.getUID() + " or a.managerid = " + user.getUID() + ")";
                    }
                }
                if (isNoAccount.equals("false")) {
                    if (recordSet.getDBType().equalsIgnoreCase("sqlserver")
                            || recordSet.getDBType().equalsIgnoreCase("mysql")) {
                        sql += " and (loginId is not null and loginId<>'')";
                    } else if (recordSet.getDBType().equalsIgnoreCase("postgresql")) {
                        sql += " and (loginId is not null and loginId<>'')";
                    } else {
                        sql += " and (loginId is not null)";
                    }
                }
                if(resourceStatus.length()>0){
                    if (!resourceStatus.equals("8") && !resourceStatus.equals("9")) {
                        sql += " and a.status = "+resourceStatus+ "";
                    }else if (resourceStatus.equals("8")) {
                        sql += " and (a.status = 0 or a.status = 1 or a.status = 2 or a.status = 3) ";
                    }
                }
                //考勤报表共享设置
                if (!rightStr.equals("") && !dataScope.equals("4")) {
                    sql += rightStr;
                }
                sql += " order by dspOrder,hrmResourceId ";
                recordSet.executeQuery(sql);
                while (recordSet.next()) {
                    String hrmResourceId = recordSet.getString("hrmResourceId");
                    //所属年份
                    String belongYear = recordSet.getString("belongYear");
                    //所属月份
                    String belongMonth = recordSet.getString("belongMonth");
                    //失效日期
                    String effectiveDate = recordSet.getString("effectiveDate");
                    //失效日期
                    String expirationDate = recordSet.getString("expirationDate");
                    String jqStatus = recordSet.getString("status");
                    /*判断假期余额的有效期*/
                    boolean status = KQBalanceOfLeaveBiz.getBalanceStatus(leaveRulesId, hrmResourceId, belongYear, currentDate, effectiveDate, expirationDate);
                    // 外籍年假使用status判断状态
                    if (isWjnj) {
                        if (StringUtils.isEmpty(jqStatus) || !StringUtils.equals(jqStatus, "1")) {
                            status = true;
                        } else {
                            status = false;
                        }
                    }
                    if (!status) {
                        continue;
                    }
                    BigDecimal baseAmount = new BigDecimal(Util.getDoubleValue(recordSet.getString("baseAmount"), 0.00));//假期基数
                    BigDecimal usedAmount = new BigDecimal(Util.getDoubleValue(recordSet.getString("usedAmount"), 0.00));//已用假期
                    BigDecimal extraAmount = new BigDecimal(Util.getDoubleValue(recordSet.getString("extraAmount"), 0.00));//额外假期
                    BigDecimal baseAmount2 = new BigDecimal(Util.getDoubleValue(recordSet.getString("baseAmount2"), 0.00));//用于混合模式时：福利年假基数
                    BigDecimal usedAmount2 = new BigDecimal(Util.getDoubleValue(recordSet.getString("usedAmount2"), 0.00));//用于混合模式时：已用福利年假
                    BigDecimal extraAmount2 = new BigDecimal(Util.getDoubleValue(recordSet.getString("extraAmount2"), 0.00));//用于混合模式时：额外福利年假

                    BigDecimal restAmount = new BigDecimal(0);
                    if (isMixMode) {
                        /*释放规则*/
                        if (KQBalanceOfLeaveBiz.CheckNj(leaveRulesId)){
                            if (StringUtils.isNotEmpty(belongMonth)) {
                                baseAmount = KQBalanceOfLeaveBiz.getCanUseAmountNew(hrmResourceId, leaveRulesId, belongYear, baseAmount, "legal", currentDate, belongMonth);
                                baseAmount2 = KQBalanceOfLeaveBiz.getCanUseAmountNew(hrmResourceId, leaveRulesId, belongYear, baseAmount2, "welfare", currentDate, belongMonth);
                            } else {
                                baseAmount = KQBalanceOfLeaveBiz.getCanUseAmount(hrmResourceId, leaveRulesId, belongYear, baseAmount, "legal", currentDate);
                                baseAmount2 = KQBalanceOfLeaveBiz.getCanUseAmount(hrmResourceId, leaveRulesId, belongYear, baseAmount2, "welfare", currentDate);
                            }
                        } else {
                            baseAmount = KQBalanceOfLeaveBiz.getCanUseAmount(hrmResourceId, leaveRulesId, belongYear, baseAmount, "legal", currentDate);
                            baseAmount2 = KQBalanceOfLeaveBiz.getCanUseAmount(hrmResourceId, leaveRulesId, belongYear, baseAmount2, "welfare", currentDate);
                        }

                        restAmount = baseAmount.add(extraAmount).subtract(usedAmount).add(baseAmount2).add(extraAmount2).subtract(usedAmount2);
                    } else {
                        /*释放规则*/
                        if (KQBalanceOfLeaveBiz.CheckNj(leaveRulesId)){
                            if (StringUtils.isNotEmpty(belongMonth)) {
                                baseAmount = KQBalanceOfLeaveBiz.getCanUseAmountNew(hrmResourceId, leaveRulesId, belongYear, baseAmount, "", currentDate, belongMonth);
                            } else {
                                baseAmount = KQBalanceOfLeaveBiz.getCanUseAmount(hrmResourceId, leaveRulesId, belongYear, baseAmount, "", currentDate);
                            }
                        } else {
                            baseAmount = KQBalanceOfLeaveBiz.getCanUseAmount(hrmResourceId, leaveRulesId, belongYear, baseAmount, "", currentDate);
                        }

                        restAmount = baseAmount.add(extraAmount).subtract(usedAmount);
                    }
                    String key = hrmResourceId + "_" + leaveRulesId;
                    if ((!CollectionUtils.isEmpty(balanceMap)) && balanceMap.containsKey(key)) {
                        BigDecimal bigDecimal = balanceMap.get(key);
                        if (bigDecimal != null) {
                            balanceMap.put(key, bigDecimal.add(restAmount));
                        } else {
                            balanceMap.put(hrmResourceId + "_" + leaveRulesId, restAmount);
                        }
                    } else {
                        balanceMap.put(hrmResourceId + "_" + leaveRulesId, restAmount);
                    }
                }
            }

            /**
             * 用于拼凑查询结果列表的表数据
             */
            SubCompanyComInfo subCompanyComInfo = new SubCompanyComInfo();
            DepartmentComInfo departmentComInfo = new DepartmentComInfo();
            JobTitlesComInfo jobTitlesComInfo = new JobTitlesComInfo();

            index = 0;
            String sql = "select * from HrmResource a where 1=1";
            if (dataScope.equals("0")) {
                //总部
            } else if (dataScope.equals("1")) {
                sql += " and a.subcompanyId1 in (" + subcomId + ") ";
            } else if (dataScope.equals("2")) {
                sql += " and a.departmentId in (" + deptId + ") ";
            } else if (dataScope.equals("3")) {
                sql += " and a.id in (" + resourceId + ")";
            } else if (dataScope.equals("4")) {
                if (allLevel.equals("1")) {
                    sql += " and (a.id=" + user.getUID() + " or a.managerStr like '%," + user.getUID() + ",%' )";
                } else {
                    sql += " and (a.id=" + user.getUID() + " or a.managerid = " + user.getUID() + ")";
                }
            }
            RecordSet recordSet = new RecordSet();
            if (isNoAccount.equals("false")) {
                if (recordSet.getDBType().equalsIgnoreCase("sqlserver")
                        || recordSet.getDBType().equalsIgnoreCase("mysql")) {
                    sql += " and (a.loginId is not null and a.loginId<>'')";
                }else if (recordSet.getDBType().equalsIgnoreCase("postgresql")) {
                    sql += " and (a.loginId is not null and a.loginId<>'')";
                } else {
                    sql += " and (a.loginId is not null)";
                }
            }
            if(resourceStatus.length()>0){
                if (!resourceStatus.equals("8") && !resourceStatus.equals("9")) {
                    sql += " and a.status = "+resourceStatus+ "";
                }else if (resourceStatus.equals("8")) {
                    sql += " and (a.status = 0 or a.status = 1 or a.status = 2 or a.status = 3) ";
                }
            }
            //考勤报表共享设置
            if (!rightStr.equals("") && !dataScope.equals("4")) {
                sql += rightStr;
            }
            sql += " order by dspOrder,id ";
            recordSet.executeQuery(sql);

            // #1475814-概述：满足考勤报分部部门显示及导出时显示全路径
            String fullPathMainKey = "show_full_path";
            KQSettingsComInfo kqSettingsComInfo = new KQSettingsComInfo();
            String isShowFullPath = Util.null2String(kqSettingsComInfo.getMain_val(fullPathMainKey),"0");
            String subCompanyNameTmp = "";
            String departmentNameTmp = "";

            while (recordSet.next()) {
                HSSFRow row = sheet.createRow(++index);

                String id = recordSet.getString("id");
                String lastName = recordSet.getString("lastName");
                String departmentId = recordSet.getString("departmentId");
                String subcompanyId = recordSet.getString("subcompanyId1");
                String jobtitleId = recordSet.getString("jobtitle");
                String workcode = recordSet.getString("workcode");

                int cellIndex = 0;
                HSSFCell cell = row.createCell(cellIndex);
                cell.setCellValue(Util.formatMultiLang(lastName, "" + user.getLanguage()));
                cellIndex++;
                if (displayColumnList.indexOf("subcom") > -1) {
                    cell = row.createCell(cellIndex);

                    // 导出时，是否显示全路径
                    subCompanyNameTmp = "1".equals(isShowFullPath) ?
                            SubCompanyComInfo.getSubcompanyRealPath(subcompanyId, "/", "0") :
                            subCompanyComInfo.getSubCompanyname(subcompanyId);
                    cell.setCellValue(Util.formatMultiLang(subCompanyNameTmp, "" + user.getLanguage()));

                    // cell.setCellValue(Util.formatMultiLang(subCompanyComInfo.getSubcompanyname(subcompanyId), "" + user.getLanguage()));
                    cellIndex++;
                }
                if (displayColumnList.indexOf("dept") > -1) {
                    cell = row.createCell(cellIndex);

                    // 导出时，是否显示全路径
                    departmentNameTmp = "1".equals(isShowFullPath) ?
                            departmentComInfo.getDepartmentRealPath(departmentId, "/", "0") :
                            departmentComInfo.getDepartmentname(departmentId);
                    cell.setCellValue(Util.formatMultiLang(departmentNameTmp, "" + user.getLanguage()));

                    // cell.setCellValue(Util.formatMultiLang(departmentComInfo.getDepartmentname(departmentId), "" + user.getLanguage()));
                    cellIndex++;
                }
                if (displayColumnList.indexOf("jobtitle") > -1) {
                    cell = row.createCell(cellIndex);
                    cell.setCellValue(Util.formatMultiLang(jobTitlesComInfo.getJobTitlesname(jobtitleId), "" + user.getLanguage()));
                    cellIndex++;
                }
                if (displayColumnList.indexOf("workcode") > -1) {
                    cell = row.createCell(cellIndex);
                    cell.setCellValue(Util.formatMultiLang(workcode, "" + user.getLanguage()));
                    cellIndex++;
                }

                rulesComInfo.setTofirstRow();
                while (rulesComInfo.next()) {
                    /*展示列不展示，不显示*/
                    if (displayColumnList.indexOf(rulesComInfo.getId()) < 0) {
                        continue;
                    }
                    /*该假期没有启用，不显示*/
                    if (!rulesComInfo.getIsEnable().equals("1")) {
                        continue;
                    }
                    /*该假期没有开启余额限制，显示不限制余额*/
                    if (rulesComInfo.getBalanceEnable().equals("0")) {
                        cell = row.createCell(cellIndex);
                        cell.setCellValue(SystemEnv.getHtmlLabelName(389731, user.getLanguage()));
                        cellIndex++;
                        continue;
                    }
                    BigDecimal restAmount = balanceMap.get(id + "_" + rulesComInfo.getId());
                    cell = row.createCell(cellIndex);
                    cell.setCellValue(restAmount != null ? restAmount.setScale(2, RoundingMode.HALF_UP).doubleValue() : 0);
                    cellIndex++;
                }

            }

            String encodedFileName = SystemEnv.getHtmlLabelName(389441, user.getLanguage());
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//            response.setCharacterEncoding("utf-8");
            String header = request.getHeader("User-Agent").toUpperCase();
            if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
                encodedFileName = URLEncoder.encode(encodedFileName, "utf-8");
                encodedFileName = encodedFileName.replace("+", "%20"); //IE下载文件名空格变+号问题
            } else {
                encodedFileName = new String(encodedFileName.getBytes("utf-8"), "ISO_8859_1");
            }
            writeLog("balanceofleaverp>>>ExportExcelCmd>>>encodedFileName="+encodedFileName);
            response.setHeader("Content-disposition", "attachment;filename=" + encodedFileName + "excel.xls");
            response.setContentType("application/msexcel");
            response.setContentType("application/x-msdownload");
            OutputStream responseOutput = response.getOutputStream();
            workbook.write(responseOutput);
            responseOutput.flush();
        } catch (Exception e) {
            writeLog(e);
        }
        long endTime = System.currentTimeMillis();
        float seconds = (endTime - startTime) / 1000F;
        System.out.println(Float.toString(seconds) + " seconds.");
        return resultMap;
    }
}
